package com.pan.hello.mysql.jdbc;

import java.sql.*;


public class HelloMysql {

    /**
     * 获取mysql连接
     * @return
     */
    public static Connection getMysqlJdbcConnection(){
        System.out.println("start the connect```");
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost/playjava?user=root&password=123456");

            // Do something with the Connection

        } catch (SQLException ex) {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }

        return conn;
    }

    /**
     * 通过execute执行select语句
     * 可以看到通过jdbc有大量的模板代码，因此有了hibernate、mybatis、spring
     *  Hibernate, Spring's JDBC templates or MyBatis SQL Maps to reduce the amount of JDBC code for you to debug, tune, secure, and maintain
     * @param conn
     */
    public static void doSelect(Connection conn){

        // assume that conn is an already created JDBC connection (see previous examples)

        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery("SELECT user_id FROM order_tbl");
            //String user_id = rs.getString("user_id");

            System.out.println(rs.toString());

            // or alternatively, if you don't know ahead of time that
            // the query will be a SELECT...

            if (stmt.execute("SELECT user_id FROM order_tbl")) {
                rs = stmt.getResultSet();
            }

            // Now do something with the ResultSet ....
            while(rs.next()){  //通过next来索引：判断是否有下一个记录
                //rs.getInt("id"); //方法：int java.sql.ResultSet.getInt(String columnLabel) throws SQLException
                String user_id = rs.getString(1);  //方法：int java.sql.ResultSet.getInt(int columnIndex) throws SQLException
                System.out.println("user_id="+user_id);
            }
        }catch (SQLException ex){
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
        finally {
            // it is a good idea to release
            // resources in a finally{} block
            // in reverse-order of their creation
            // if they are no-longer needed

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException sqlEx) { } // ignore

                rs = null;
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlEx) { } // ignore
                stmt = null;
            }
        }
    }

    public static void main(String[] args) {
       Connection conn = getMysqlJdbcConnection();
       doSelect(conn);
    }

}
